/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.edu.fpt.xml.hrm.controller.auth;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Locale;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.xml.stream.XMLEventFactory;
import javax.xml.stream.XMLEventWriter;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.events.XMLEvent;
import vn.edu.fpt.xml.hrm.common.FormatUtil;

/**
 *
 * @author AnhNH60400
 */
public class LoginObjJDBC implements Serializable {

    private String username;
    private String password;
    private String displayname;
    private String employeeid;
    private String failed;

    public String getFailed() {
        return failed;
    }

    public void setFailed(String failed) {
        this.failed = failed;
    }



    public String getEmployeeid() {
        return employeeid;
    }

    public void setEmployeeid(String employeeid) {
        this.employeeid = employeeid;
    }

    /**
     * @return the username
     */
    public String getUsername() {
        return username;
    }

    public String getDisplayname() {
        return displayname;
    }

    public void setDisplayname(String displayname) {
        this.displayname = displayname;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    public LoginObjJDBC() {
    }

    public LoginObjJDBC(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public boolean checkLogin() {
        try {
            Context ctx = new InitialContext(); // khai bao binding object va database
            Context envCtx = (Context) ctx.lookup("java:comp/env");//lay ten object
            DataSource ds = (DataSource) envCtx.lookup("DBCon");// tao factory connect object
            Connection con = ds.getConnection();// lay connection
            //System.out.println("Connection String: " + con.toString());
//            String sql = "SELECT e.EmployeeID, u.DisplayName, u.UserName, u.Password "// lay employeeID & displayName
//                    + "FROM Employee e INNER JOIN \"User\" u ON e.UserID = u.UserID "// tu bang employee join user tren UserID bang nhau
//                    + "WHERE u.Status = 'Approved'";
//                    + "WHERE u.UserName = ? and u.Password = ? and u.Status = 'Approved'";// dieu kien dung username, pass, va status la approved
            String sql = "SELECT e.EmployeeID, u.DisplayName, u.UserName, u.Password "// lay employeeID & displayName
                    + "FROM Employee e INNER JOIN \"User\" u ON e.UserID = u.UserID "// tu bang employee join user tren UserID bang nhau
                    + "WHERE u.UserName = ? and u.Password = ? and u.Status = 'Approved'";
            PreparedStatement stm = con.prepareStatement(sql);// complie sql thanh object
            stm.setString(1, username);
            stm.setString(2, password);
            
            ResultSet rs = stm.executeQuery();// thuc hien cau lenh query
            boolean result = false;
            while (rs.next() && result == false) {
                String user = rs.getString("UserName");
                String pass = rs.getString("Password");
                
                if (user.equals(username) && pass.equals(password)) {
                    result = true;
                    displayname = rs.getString("DisplayName");// lay tri o displayName gan vao bien
                    employeeid = rs.getString("EmployeeID");
                }
            }
            rs.close();
            stm.close();
            con.close();
            if (result) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //failed = "FAILED";
        return false;
    }

     public static void createXMLPayroll(String filepath, 
             Date fromDate, Date toDate) throws NamingException, SQLException,
             IOException, XMLStreamException {

        Context ctx = new InitialContext();
        Context envCtx = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) envCtx.lookup("DBCon");
        Connection con = ds.getConnection();

        String sql = "SELECT e.EmployeeID, e.EmployeeName, tmp.WorkingHour, "
                + "tmp.WorkingHour * e.HourlyWages AS 'Payment' "
                + "FROM Employee e "
                + "INNER JOIN "
                + "( SELECT d.EmployeeID, SUM(d.Duration) AS 'WorkingHour' "
                + "FROM DailyReport d  "
                + "WHERE  d.Date >= ? AND d.Date <= ? "
                + "GROUP BY d.EmployeeID ) tmp "
                + "ON e.EmployeeID = tmp.EmployeeID";
//         if ((empId > 0)) {
//             sql += " WHERE e.EmployeeID = ?";
//         }

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setDate(1, fromDate);
        ps.setDate(2, toDate);
//         if ((empId > 0)) {
//             ps.setInt(3, empId);
//         }
        
        ResultSet rs = ps.executeQuery();

        XMLOutputFactory xof = XMLOutputFactory.newInstance();
        XMLEventFactory xef = XMLEventFactory.newInstance();

        FileWriter file = new FileWriter(filepath);
        XMLEventWriter writer = xof.createXMLEventWriter(file);
        XMLEvent event = xef.createStartDocument();
        writer.add(event);
        //start root
        event = xef.createStartElement("", null, "payrolls");
        writer.add(event);

        while (rs.next()) {

            String id = rs.getString("EmployeeID");
            String name = rs.getString("EmployeeName");
//            String date = FormatUtil.formatDate(FormatUtil.dateToDate(rs.getDate("Date")), "yyyy-MM-dd", Locale.ENGLISH);
            String workingHour = rs.getString("WorkingHour");
            Double payment = rs.getDouble("Payment");

            //payroll
            event = xef.createStartElement("", null, "payroll");
            writer.add(event);
            event = xef.createAttribute("id", id);
            writer.add(event);

            //employeeID
            event = xef.createStartElement("", null, "employeeID");
            writer.add(event);
            event = xef.createCharacters(String.valueOf(id));
            writer.add(event);
            event = xef.createEndElement("", null, "employeeID");
            writer.add(event);

            //employeeName
            event = xef.createStartElement("", null, "employeeName");
            writer.add(event);
            event = xef.createCharacters(name);
            writer.add(event);
            event = xef.createEndElement("", null, "employeeName");
            writer.add(event);

            //date
//            event = xef.createStartElement("", null, "date");
//            writer.add(event);
//            event = xef.createCharacters(date);
//            writer.add(event);
//            event = xef.createEndElement("", null, "date");
//            writer.add(event);

            //workingHour
            event = xef.createStartElement("", null, "workingHour");
            writer.add(event);
            event = xef.createCharacters(workingHour);
            writer.add(event);
            event = xef.createEndElement("", null, "workingHour");
            writer.add(event);

            //payment
            event = xef.createStartElement("", null, "payment");
            writer.add(event);
            event = xef.createCharacters(String.valueOf(payment));
            writer.add(event);
            event = xef.createEndElement("", null, "payment");
            writer.add(event);

            event = xef.createEndElement("", null, "payroll");
            writer.add(event);
        }

        //end root
        event = xef.createEndElement("", null, "payrolls");
        writer.add(event);
        writer.flush();
        writer.close();
    }

    public boolean isAdmin() {
        boolean Manager = false;
        PreparedStatement sta = null;
        ResultSet rs = null;
        try {
            Context ctx = new InitialContext();
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("DBCon");
            Connection con = ds.getConnection();
            String sql = "Select * from \"User\" where UserName = ? and Password = ?";
            sta = con.prepareStatement(sql);
            sta.setString(1, username);
            sta.setString(2, password);
            rs = sta.executeQuery();
            if (rs.next()) {
                Manager = rs.getBoolean("isAdmin");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (sta != null) {
                    sta.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return Manager;
    }

    public String checkStatus() {
        String Status = "";
        PreparedStatement sta = null;
        ResultSet rs = null;
        try {
            Context ctx = new InitialContext();
            Context envCtx = (Context) ctx.lookup("java:comp/env");
            DataSource ds = (DataSource) envCtx.lookup("DBCon");
            Connection con = ds.getConnection();
            String sql = "Select * from \"User\" where username = ? and password = ? ";
            sta = con.prepareStatement(sql);
            sta.setString(1, username);
            sta.setString(2, password);
            rs = sta.executeQuery();
            if (rs.next()) {
                Status = rs.getString("Status");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (sta != null) {
                    sta.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        return Status;
    }
//    public boolean getEmployeeID() {
//        try {
//            Context ctx = new InitialContext();
//            Context envCtx = (Context) ctx.lookup("java:comp/env");
//            DataSource ds = (DataSource) envCtx.lookup("DBCon");
//            Connection con = ds.getConnection();
//            System.out.println("Connection String: " + con.toString());
//            String sql = "SELECT EmployeeID FROM Employee WHERE (SELECT UserID FROM \"User\" WHERE \"User\".UserName = ?)= Employee.UserID";
//            PreparedStatement stm = con.prepareStatement(sql);
//            stm.setString(1, username);
//            stm.setString(2, password);
//            ResultSet rs = stm.executeQuery();
//            boolean result = rs.next();
//            employeeid= rs.getString("EmployeeID");
//            rs.close();
//            stm.close();
//            con.close();
//            if (result) {
//                return true;
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return false;
//    }
}
